﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportReport8 : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_风险部分期汇总.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "分期汇总";
            }

        }
        protected string GetDateFrom
        {
            get
            {
                return DateTime.Now.ToString("yyyy-MM-01");
            }
        }
        /// <summary>
        /// 当月最后一天
        /// </summary>
        protected DateTime GetDateTo
        {
            get
            {
                return Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-01")).AddDays(-1);
            }
        }

        private DataTableCollection _dts = null;
        protected DataTableCollection dts
        {
            get
            {
                if (_dts == null)
                {
                    //营业经理权限 2014-02-21
                    string agentList = base.FTradeAgentPowerSqlWhere(UserId);
                    string wheresql = string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" JingXiaoShang in ({0})", agentList);

                    _dts = FTradeReportController.Report8GetReportDate(wheresql,GetDateTo.ToString("yyyy-MM-dd 23:59:59"));
                }
                return _dts;
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //
            DataTable DaoQiRidt = dts[0];
            DateTime minDaoQiRi;
            DateTime maxDaoQiRi;
            if (DaoQiRidt.Rows.Count <= 0)
            {
                minDaoQiRi = DateTime.Now;
                maxDaoQiRi = DateTime.Now;
            }
            else
            {
                minDaoQiRi = DaoQiRidt.Rows[0]["minDaoQiRi"] is DBNull ? DateTime.Now : Convert.ToDateTime(DaoQiRidt.Rows[0]["minDaoQiRi"]);
                maxDaoQiRi = DaoQiRidt.Rows[0]["maxDaoQiRi"] is DBNull ? DateTime.Now : Convert.ToDateTime(DaoQiRidt.Rows[0]["maxDaoQiRi"]);
            }
            //获取帐龄明细
            DataTable AgeDetailJinEDT = dts[2];
            //到期日列添加
            DataTable dtList = dts[1];
            DataTable dtSumList = dts[3];
            //写标题
            sheet1.GetRow(0).CreateCell(0).SetCellValue(string.Format("分期汇总(短期分期，置换，首付代垫)", GetDateTo.ToString("yyyy年MM月dd日")));
            //20140423写入标题
            sheet1.GetRow(1).CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy-MM") + "前未付首付");
            sheet1.GetRow(1).CreateCell(3).SetCellValue(DateTime.Now.ToString("yyyy-MM") + "前其它未付");
            sheet1.GetRow(1).CreateCell(4).SetCellValue(DateTime.Now.ToString("yyyy-MM")+"前未付");

            int titleRowColumnIndex = 5;
            for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
            {
                sheet1.GetRow(1).CreateCell(titleRowColumnIndex).SetCellValue(dtTemp.ToString("yyyy-MM"));
                titleRowColumnIndex++;
            }

            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;

            foreach (DataRow listDTDR in dtList.Rows)
            {
                HSSFRow writerow = (HSSFRow)sheet1.CreateRow(rowReal);
                writerow.CreateCell(0).SetCellValue(listDTDR["经销商"].ToString());
                writerow.CreateCell(1).SetCellValue(GetDBDouble(listDTDR["总额"]));

                writerow.CreateCell(2).SetCellValue(GetDBDouble(listDTDR["首付逾期上月前"]));
                writerow.CreateCell(3).SetCellValue(GetDBDouble(listDTDR["分期逾期上月前"]));
                writerow.CreateCell(4).SetCellValue(GetDBDouble(listDTDR["分期逾期上月前所有"]));

                //获取列值
                titleRowColumnIndex =5;
                for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
                {
                    string startDT = dtTemp.ToString("yyyy-MM-01");
                    string endDT = Convert.ToDateTime(dtTemp.AddMonths(1).ToString("yyyy-MM-01")).AddDays(-1).ToString("yyyy-MM-dd");

                    object JinE = AgeDetailJinEDT.Compute("sum(JinE)", string.Format(" JingXiaoShang='{0}' and DaoQiRi >= '{1}' and DaoQiRi<='{2}'", listDTDR["经销商"], startDT, endDT));
                    writerow.CreateCell(titleRowColumnIndex).SetCellValue(GetDBDouble(JinE));
                    titleRowColumnIndex++;
                }
                rowReal++;
            }

            HSSFRow writerowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            writerowSum.CreateCell(0).SetCellValue("总计");
            writerowSum.CreateCell(1).SetCellFormula(string.Format("sum(B3:B{0})", rowReal));
            writerowSum.CreateCell(2).SetCellFormula(string.Format("sum(C3:C{0})", rowReal));
            writerowSum.CreateCell(3).SetCellFormula(string.Format("sum(D3:D{0})", rowReal));
            writerowSum.CreateCell(4).SetCellFormula(string.Format("sum(E3:E{0})", rowReal));
            titleRowColumnIndex = 5;
            for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
            {
                writerowSum.CreateCell(titleRowColumnIndex).SetCellFormula(string.Format("sum({0}3:{0}{1})", GetCellName(titleRowColumnIndex), rowReal));
                titleRowColumnIndex++;
            }

            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
        }

        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", "分期汇总表", DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}